﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.Models;
using UncleBobWebService.ServiceRequester;



namespace UncleBobWebService.Database
{
    class JobApplicationDBAdapter : IDBAdapter 
    {
        public override ServiceRequester.Message CreateData(ServiceRequester.Message msg, object data)
        {
            msg.Result = true;

            JobApplication jobApp = (JobApplication)msg.Data;            

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = string.Format("INSERT INTO JobApplications (ApplyDate, UserID, JobID, Reviewed, Accepted, Hired) VALUES ('{0}', {1}, {2}, '{3}', '{4}', '{5}')"
                            , jobApp.ApplyDate, jobApp.UserID, jobApp.Job.ID, false, false, false);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();                    
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }
                finally
                {
                    connection.Close();
                }


                DataTable dt = new DataTable();
                command.CommandText = string.Format("SELECT * FROM JobApplications WHERE UserID = {0} AND JobID = {1}"
                                , jobApp.UserID, jobApp.Job.ID);

                try
                {
                    SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }

                finally
                {
                    connection.Close();
                }

                if (dt.Rows.Count >= 1)
                {
                    jobApp.AppID = (int)dt.Rows[0]["JobApplicationID"];
                }
                else
                {
                    jobApp.AppID  = -1;
                    msg.Result = false;
                }
            }

            return msg;
        }

        public override ServiceRequester.Message SearchForSpecificData(ServiceRequester.Message msg, object data)
        {
            msg.Result = true;
            JobApplication jobApp = msg.Data as JobApplication;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                DataTable dt = new DataTable();

                command.CommandText = string.Format("SELECT * FROM JobApplications WHERE JobApplicationID = {0}", jobApp.AppID);

                try
                {
                    SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }

                finally
                {
                    connection.Close();
                }

                if (dt.Rows.Count >= 1)
                {
                    jobApp.ApplyDate = Convert.ToDateTime(dt.Rows[0]["ApplyDate"]);
                    jobApp.Reviewed = Convert.ToBoolean(dt.Rows[0]["Reviewed"]);
                    jobApp.Approved = Convert.ToBoolean(dt.Rows[0]["Accepted"]);
                    jobApp.Rejected = Convert.ToBoolean(dt.Rows[0]["Rejected"]);
                    jobApp.Hired = Convert.ToBoolean(dt.Rows[0]["Hired"]);
                    jobApp.Job.ID = Convert.ToInt32(dt.Rows[0]["JobID"]);
                    jobApp.AppID = Convert.ToInt32(dt.Rows[0]["JobApplicationID"]);
                    jobApp.CommentContainer.AppID = jobApp.AppID;
                    jobApp.CommentContainer.UserID = jobApp.UserID;
                }


                HMCommentDBAdapter dbAdapter = new HMCommentDBAdapter();
                Message tempMsg = new Message();
                tempMsg.Data = jobApp.CommentContainer;
                tempMsg = dbAdapter.SearchForAllData(tempMsg);

                jobApp.CommentContainer = tempMsg.Data as CommentModel;

            }

            return msg;
        }

        public override ServiceRequester.Message SearchForAllData(ServiceRequester.Message msg)
        {
            msg.Result = true;
            RegisterModel regMod = (RegisterModel)msg.Data;
            List<JobApplication> jobApps = new List<JobApplication>();

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                DataTable dt = new DataTable();

                command.CommandText = string.Format("SELECT * FROM JobApplications WHERE UserID = {0}", regMod.UserID);

                try
                {
                    SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }

                finally
                {
                    connection.Close();
                }

                foreach (DataRow dr in dt.Rows)
                {
                    JobApplication tempJobApp = new JobApplication();

                    tempJobApp.ApplyDate = Convert.ToDateTime(dr["ApplyDate"]);
                    tempJobApp.Reviewed = Convert.ToBoolean(dr["Reviewed"]);
                    tempJobApp.Approved = Convert.ToBoolean(dr["Accepted"]);
                    tempJobApp.Hired = Convert.ToBoolean(dr["Hired"]);
                    tempJobApp.Rejected = Convert.ToBoolean(dr["Rejected"]);
                    tempJobApp.AppID = Convert.ToInt32(dr["JobApplicationID"]);
                    tempJobApp.Job.ID = Convert.ToInt32(dr["JobID"]);
                    tempJobApp.UserID = regMod.UserID;
                    tempJobApp.CommentContainer.AppID = tempJobApp.AppID;
                    tempJobApp.CommentContainer.UserID = tempJobApp.UserID;

                    HMCommentDBAdapter dbAdapter = new HMCommentDBAdapter();
                    Message tempMsg = new Message();
                    tempMsg.Data = tempJobApp.CommentContainer;
                    tempMsg = dbAdapter.SearchForAllData(tempMsg);

                    tempJobApp.CommentContainer = tempMsg.Data as CommentModel;

                    jobApps.Add(tempJobApp);
                }

                msg.Data = jobApps;
            }

            return msg;
        }

        public override ServiceRequester.Message EditSpecificData(ServiceRequester.Message msg, object data)
        {
            msg.Result = true;
            JobApplication jobApp = msg.Data as JobApplication;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                DataTable dt = new DataTable();

                command.CommandText = string.Format("UPDATE JobApplications SET Reviewed='{0}', Accepted='{1}', Hired='{2}', Rejected='{3}' WHERE JobApplicationID = {4}",
                                                     jobApp.Reviewed, jobApp.Approved, jobApp.Hired, jobApp.Rejected, jobApp.AppID);
                try
                {
                    SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }

                finally
                {
                    connection.Close();
                }
            }

            return msg;
        }

        public override ServiceRequester.Message DeleteSpecificData(ServiceRequester.Message msg, object data)
        {
            throw new NotImplementedException();
        }

        public override ServiceRequester.Message DeleteAllData(ServiceRequester.Message msg)
        {
            throw new NotImplementedException();
        }
    }
}
